USE [NANS21V]
GO

/****** オブジェクト: SqlProcedure [dbo].[TaikouKyogi] スクリプト日付: 2014/05/14 16:13:26 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TaikouKyogi]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[TaikouKyogi];
GO

CREATE PROCEDURE [dbo].[TaikouKyogi]
	@TAIKAINO int,
	@TAIKOSENNO int
AS
SELECT 
	TKYOGI.KYOGINO, 
	TKYOGI.KYOGIMEI, 
	( SELECT 
		MAX(TKUMI.ROUNDNO) 
	FROM 
		TBL_KUMI AS TKUMI 
	WHERE 
		TKUMI.TAIKAINO = TKYOGI.TAIKAINO 
		AND TKUMI.KYOGINO = TKYOGI.KYOGINO ) AS LASTROUND, 
		( CASE WHEN 0 = 
			( SELECT 
				COUNT(TKUMI.KUMINO) 
			FROM 
				TBL_KUMI AS TKUMI 
			WHERE 
				TKUMI.TAIKAINO = TKYOGI.TAIKAINO 
				AND TKUMI.KYOGINO = TKYOGI.KYOGINO 
				AND TKUMI.KYOGIFLG != 1 ) THEN 1 ELSE 0 END ) AS KAKUTEI, 
				( SELECT 
					COUNT(TKOBETU.SYUKEINO) 
				FROM 
					TBL_TAIKOU_KOBETU AS TKOBETU 
				WHERE 
					TKOBETU.TAIKAINO = TTAIKOUKYOGI.TAIKAINO 
					AND TKOBETU.TAIKOSENNO = TTAIKOUKYOGI.TAIKOSENNO 
					AND TKOBETU.KYOGINO = TTAIKOUKYOGI.KYOGINO ) AS SYUKEI 
FROM 
	TBL_TAIKOU_KYOGI AS TTAIKOUKYOGI 
	LEFT OUTER JOIN 
	( SELECT
		TKYOGI.TAIKAINO, 
		TKYOGI.KYOGINO, 
		TKYOGI.KYOGIMEI, 
		TKYOGI.GATTAIFLG, 
		TKYOGI.SEIBETUNO, 
		TSYUBETU.PRINTNO AS SYUBETUPRINTNO, 
		TSYUMOKU.PRINTNO AS SYUMOKUPRINTNO 
	FROM 
		TBL_KYOGI AS TKYOGI, 
		TBL_SYUBETU AS TSYUBETU, 
		TBL_SYUMOKU_MASTER AS TSYUMOKU 
	WHERE 
		TKYOGI.TAIKAINO = @TAIKAINO 
		AND TKYOGI.SYUBETUNO = TSYUBETU.SYUBETUNO 
		AND TSYUMOKU.SYUMOKUNO = TKYOGI.SYUMOKUNO ) AS TKYOGI 
ON 
	TKYOGI.TAIKAINO = TTAIKOUKYOGI.TAIKAINO 
	AND TKYOGI.KYOGINO = TTAIKOUKYOGI.KYOGINO 
WHERE 
	TTAIKOUKYOGI.TAIKAINO = @TAIKAINO 
	AND TTAIKOUKYOGI.TAIKOSENNO = @TAIKOSENNO 
	AND (TKYOGI.GATTAIFLG = 0 OR TKYOGI.GATTAIFLG = 2) 
ORDER BY 
	TKYOGI.SEIBETUNO, 
	TKYOGI.SYUBETUPRINTNO, 
	TKYOGI.SYUMOKUPRINTNO
